package com.cogent.dao.orm;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.sql.DataSource;

import org.apache.log4j.Logger;

import com.cogent.common.exception.SystemRuntimeException;
import com.cogent.common.util.StringUtil;
import com.cogent.dao.domain.ParamDomain;

public abstract class AbstractDBOperator implements DBOperator {
	//日志对象
	private Logger _log = Logger.getLogger(AbstractDBOperator.class); 
	
	//数据库连接池对象
	private DataSource dataSource;	
	
	private String userName;

	//数据库连接对象
	protected Connection _con;
	
	/**
	 * 分页查询时，是否进行统计
	 */
	private boolean pageCount = true;
	
	//打开数据库连接
	public void openDB() {
		try {
			_con = getDataSource().getConnection();
		} catch (SQLException e) {
			_log.error("在打开数据库连接时处发生错误，错误信息为：" + e.getMessage());
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, e, "err.system.msg.dboperator");
		}
	}
	
	//关闭数据库连接
	public void closeDB() {
		try {
			if(_con != null)
				_con.close();
			_con = null;
		} catch (SQLException e) {
			_log.error("在关闭数据库连接时处发生错误，错误信息为：" + e.getMessage());
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, e, "err.system.msg.dboperator");
		}
	}
	
	/**
	 * 执行SQL语句
	 * @param sql 待执行的SQL语句
	 * @param param 待绑定的参数变量
	 */
	public void executeSql(String sql, List<ParamDomain> param) {
		PreparedStatement stat = null;
		try {
			stat = _con.prepareStatement(sql);
			bingParam(stat, param);
			stat.executeUpdate();
		} catch (SQLException e) {
			_log.error("在执行SQL语句"+ sql + "参数为" + param.toString() 
					+ "时发生错误，错误信息为：" + e.getMessage());
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, e, "err.system.msg.dboperator");
		}
		finally{
			closeStat(stat);
		}
	}
	
	/**
	 * 执行SQL语句
	 * @param sql待执行的SQL语句
	 */
	public void executeSql(String sql) {
		Statement stat = null;
		try {
			stat = _con.createStatement();
			stat.executeUpdate(sql);
		} catch (SQLException e) {
			_log.error("在执行SQL语句"+ sql + "时发生错误，错误信息为：" + e.getMessage());
			e.printStackTrace();
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, e, "err.system.msg.dboperator");
		}
		finally{
			closeStat(stat);
		}
	}
	
	/**
	 * 按主键查询数据
	 * @param sql 待执行的SQL
	 * @param queryList 待绑定的参数变量
	 * @return 列名，列值的键值对
	 */
	public HashMap<String, Object> getByID(String sql, List<ParamDomain> queryList) {
		PreparedStatement stat = null;
		ResultSet rs = null;
		try {
			stat = _con.prepareStatement(sql);
			bingParam(stat, queryList);
			rs = stat.executeQuery();
			return resultSetToHashMap(getTableName(sql), rs);
		} catch (SQLException e) {
			_log.error("在执行SQL语句"+ sql + "参数为" + queryList.toString() 
					+ "时发生错误，错误信息为：" + e.getMessage());
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, e, "err.system.msg.dboperator");
		}
		finally{
			closeStat(rs, stat);	
		}
	}
	
	/**
	 * 执行查询SQL，只获取第一行
	 * @param sql 待执行SQL
	 * @return 返回第一行，格式为列名，列值的键值对
	 */
	public HashMap<String, Object> getOneRow(String sql) {
		Statement stat = null;
		ResultSet rs = null;
		try {
			stat = _con.createStatement();
			rs = stat.executeQuery(sql);
			return resultSetToHashMap(getTableName(sql), rs);
		} catch (SQLException e) {
			_log.error("在执行SQL语句"+ sql + "时发生错误，错误信息为：" + e.getMessage());
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, e, "err.system.msg.dboperator");
		}
		finally{
			closeStat(rs, stat);	
		}
	}
	
	/**
	 * 执行查询SQL，返回多行
	 * @param sql 待执行的SQL
	 * @param queryList 待绑定的参数变量
	 * @return 返回多行，每一行为列名，列值的键值对
	 */
	public List<HashMap<String, Object>> query(String sql,  List<ParamDomain> queryList) {
		PreparedStatement stat = null;
		ResultSet rs = null;
		try {
			stat = _con.prepareStatement(sql);
			bingParam(stat, queryList);
			rs = stat.executeQuery();
			return resultSetToList(getTableName(sql), rs);
		} catch (SQLException e) {
			_log.error("在执行SQL语句"+ sql + "参数为" + queryList.toString() 
					+ "时发生错误，错误信息为：" + e.getMessage());
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, e, "err.system.msg.dboperator");
		}
		finally{
			closeStat(rs, stat);
		}
	}
	
	/**
	 * 执行查询SQL，返回多行
	 * @param sql 待执行的SQL
	 * @return 返回多行，每一行为列名，列值的键值对
	 */
	public List<HashMap<String, Object>> query(String sql) {
		Statement stat = null;
		ResultSet rs = null;
		try {
			stat = _con.createStatement();
			rs = stat.executeQuery(sql);
			return resultSetToList(getTableName(sql), rs);
		} catch (SQLException e) {
			_log.error("在执行SQL语句"+ sql + "时发生错误，错误信息为：" + e.getMessage());
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, e, "err.system.msg.dboperator");
		}
		finally{
			closeStat(rs, stat);
		}
	}
	
	/**
	 * 开启事务
	 */
	public void beginTran() {
		try {
			_con.setAutoCommit(false);
		} catch (SQLException e) {
			_log.error("开启事务时发生异常，错误信息为：" + e.getMessage());
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, e, "err.system.msg.dboperator");
		}
	}
	
	/**
	 * 提交事务
	 */
	public void commitTran() {
		try {
			_con.commit();
		} catch (SQLException e) {
			_log.error("提交事务时发生异常，错误信息为：" + e.getMessage());
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, e, "err.system.msg.dboperator");
		}
	}
	
	/**
	 * 回滚事务
	 */
	public void rollbackTran() {
		try {
			_con.rollback();
		} catch (SQLException e) {
			_log.error("回滚事务时发生异常，错误信息为：" + e.getMessage());
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, e, "err.system.msg.dboperator");
		}
	}
	
	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	
	protected abstract void bingParam(PreparedStatement stat, List<ParamDomain> paramList);
	
	protected abstract String getColumnType(String tableName, String column);
	
	protected abstract boolean isImageColumn(String tableName, String column);
	
	protected abstract boolean isNumScaleColumn(String tableName, String column);
	
	protected void closeStat(Statement stat) {
		if(stat != null)
			try {
				stat.close();
				stat = null;
			} catch (SQLException e) {
				_log.error("在关闭Statement时发生错误，错误信息为：" + e.getMessage());
				throw new SystemRuntimeException(SystemRuntimeException.HIGH, e, "err.system.msg.dboperator");
			}
	}
	
	protected void closeStat(ResultSet rs, Statement stat) {
		if(rs != null)
			try {
				rs.close();
				rs = null;
			} catch (SQLException e) {
				_log.error("在关闭ResultSet时发生错误，错误信息为：" + e.getMessage());
				throw new SystemRuntimeException(SystemRuntimeException.HIGH, e, "err.system.msg.dboperator");
			}
		if(stat != null)
			try {
				stat.close();
				stat = null;
			} catch (SQLException e) {
				_log.error("在关闭Statement时发生错误，错误信息为：" + e.getMessage());
				throw new SystemRuntimeException(SystemRuntimeException.HIGH, e, "err.system.msg.dboperator");
			}
	}
	
	private HashMap<String,Object> resultSetToHashMap(String tableName, ResultSet rs) {
		HashMap<String,Object> map = new HashMap<String,Object>();
		try {
			int num = 0;
			int cols = 0;			
			while(rs.next()) {
				if(num == 0) {
					cols = rs.getMetaData().getColumnCount();
					num = 1;
				}
				for(int i = 1; i <= cols; i++) {
					String column = rs.getMetaData().getColumnLabel(i).toLowerCase();
					if(isImageColumn(tableName, column)) {
						Blob blob = rs.getBlob(i);
						InputStream inStream =  blob.getBinaryStream();
						long nLen = blob.length();
		                int nSize = (int) nLen;
		                byte[] data = new byte[nSize];
		                inStream.read(data);
		                inStream.close();
		                map.put(column, data);
					}else if(isNumScaleColumn(tableName, column)){
						String tmp = StringUtil.doubleToString(rs.getDouble(i));
						map.put(column, tmp);
					} else {
						String tmp = rs.getString(i);
						map.put(column, tmp);
					}
				}
				break;
			}
		} catch (SQLException e) {
			_log.error("将ResultSet转换为HashMap时发生异常，错误信息为：" + e.getMessage());
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, e, "err.system.msg.dboperator");
		} catch (IOException e) {
			_log.error("将ResultSet转换为HashMap时发生异常，错误信息为：" + e.getMessage());
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, e, "err.system.msg.dboperator");
		}
		
		return map;
	}
	
	private List<HashMap<String, Object>> resultSetToList(String tableName, ResultSet rs) {
		List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
		try {
			int num = 0;
			int cols = 0;
			while(rs.next()) {
				if(num == 0) {
					cols = rs.getMetaData().getColumnCount();
					num = 1;
				}
				HashMap<String, Object> map = new HashMap<String, Object>();
				for(int i = 1; i <= cols; i++) {
					String column = rs.getMetaData().getColumnLabel(i).toLowerCase();
					String tmp = "";
					if(isNumScaleColumn(tableName, column))
						tmp = StringUtil.doubleToString(rs.getDouble(i));
					else
						tmp = rs.getString(i);
					map.put(rs.getMetaData().getColumnLabel(i).toLowerCase(), tmp);
				}
				
				list.add(map);
			}
		} catch (SQLException e) {
			_log.error("将ResultSet转换为ArrayList时发生异常，错误信息为：" + e.getMessage());
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, e, "err.system.msg.dboperator");
		}
		
		return list;
	}
	
	public String getTableName(String sql) {
		String temp = sql.toLowerCase();
		while(temp.indexOf("from (") > 0)
			temp = temp.substring(temp.indexOf("from (") + 6);
		if(temp.indexOf(" from ")>0) {
			temp = temp.substring(temp.indexOf(" from ") + 6);
			int start = temp.indexOf(" ");
			if(start == 0)
				temp = temp.substring(1);
			start = temp.indexOf(" ");
			if(start > 0)
				temp = temp.substring(0, start);
		}
		
		return temp;
	}

	public boolean isPageCount() {
		return pageCount;
	}

	public void setPageCount(boolean pageCount) {
		this.pageCount = pageCount;
	}

	public String getUserName() {
		return userName;
	}

	public void setUserName(String userName) {
		this.userName = userName;
	}
}
